﻿<?php
ini_set('memory_limit', '-1');
ini_set('max_execution_time', 86400);
            
define('_JEXEC', 1);
define('DS', DIRECTORY_SEPARATOR);

if (file_exists(dirname(__FILE__) . '/defines.php')) {
    include_once dirname(__FILE__) . '/defines.php';
}

if (!defined('_JDEFINES')) {
    define('JPATH_BASE', dirname(__FILE__));
    require_once JPATH_BASE.'/includes/defines.php';
}

require_once JPATH_BASE.'/includes/framework.php';

// Instantiate the application.
$app = JFactory::getApplication('site');

// Initialise the application.
$app->initialise();

require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'reportProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'utilities'.DS.'pointProvider.php';
require_once JPATH_BASE.DS.'administrator'.DS.'components'.DS.'com_wrd'.DS.'classes'.DS.'PHPExcel.php';

$db = JFactory::getDbo();
$conditions = array();

//echo strval($_GET['i']);exit;
$conditions['dateFrom'] = strval($_GET['f']);
$conditions['dateTo'] = strval($_GET['t']);
$limit = strval($_GET['limit']);
$baseon = $_GET['baseon'];
settype($baseon,"int");
if($baseon == 1){
	$baseonTxt = "Actual Paid";	
	
}
else if($baseon == 2){
	$baseonTxt = "Net Sale";
}
else if($baseon == 3){

	$baseonTxt = "Visited times";
}

{// Init excel
$objPHPExcel = new PHPExcel();
// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$activeSheet = $objPHPExcel->getActiveSheet();
$activeSheet->mergeCells('A1:I1');
$activeSheet->setCellValue('A1', 'Summary Member Spending Report');
$activeSheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$activeSheet->getStyle('A1')->getFont()->setSize(24);
$activeSheet->getStyle('A1')->getFont()->setBold(true);

$activeSheet->setCellValue('A2', 'Business Date From');
$activeSheet->setCellValue('B2', strval($_GET['f']));

$activeSheet->setCellValue('C2', 'Business Date To');
$activeSheet->setCellValue('D2', strval($_GET['t']));

$activeSheet->setCellValue('E2', 'Base On');
$activeSheet->setCellValue('F2', $baseonTxt);
$fill = $activeSheet->getStyle("A2:M2")->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('f7f7f7');

/************** FILL HEADER **************/
$index = 5;
$activeSheet->setCellValue('A'.$index, 'Member ID');
$activeSheet->setCellValue('B'.$index, 'Amigo');
$activeSheet->setCellValue('C'.$index, 'Kissho');
$activeSheet->setCellValue('D'.$index, 'CCAD');
$activeSheet->setCellValue('E'.$index, 'CCNH');
$activeSheet->setCellValue('F'.$index, 'Ngân Đình');
$activeSheet->setCellValue('G'.$index, 'TOTT');
$activeSheet->setCellValue('H'.$index, 'Đức Bảo');
$activeSheet->setCellValue('I'.$index, 'Spa & Massage');
$activeSheet->setCellValue('J'.$index, 'AD Food Court');
$activeSheet->setCellValue('K'.$index, 'The Club');
$activeSheet->setCellValue('L'.$index, 'America');
$activeSheet->setCellValue('M'.$index, 'Total');

$fill = $activeSheet->getStyle('A5:M5')->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('cc99ff');
$style = $activeSheet->getStyle('A3')->getFont();
$style->setSize(12);
$style->setBold(true);

$cols = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I','J','K','L','M');

foreach($cols as $item)
{
     setBackgroundColor($activeSheet, $item, 5, 20);   
}

}// End init excel

/******** Query Data ********/  

$data = reportProvider::outletreport($conditions,$db,$limit,$baseon);
if(!empty($data))
{
    $activeSheet->mergeCells('A3:H3');
    $activeSheet->setCellValue('A3', 'Total: '.$data['total'].'. Display top: '.count($data['items']));   
	$style = $activeSheet->getStyle('A3')->getFont();
    $style->setSize(16);
    $style->setBold(true);
    $style->setColor( new PHPExcel_Style_Color( 'CC0000' ) );
    /*********** BIND TO EXCEL ***********/
    $index = 7;
    $data = $data['items'];
    $size = count($data);
    for($i=0; $i<$size; $i++)
    {
		$activeSheet->setCellValue('A'.$index, $data[$i]->vip_number);
		$activeSheet->getStyle('A'.$index)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$activeSheet->getStyle("B$index:M$index")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        $objPHPExcel->getActiveSheet()->getStyle("B$index:M$index")->getNumberFormat()->setFormatCode("#,##0");
		
		$activeSheet->setCellValue('B'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=111,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
		$activeSheet->setCellValue('C'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=181,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
        $activeSheet->setCellValue('D'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=221,$conditions['dateFrom'],$conditions['dateTo'],$baseon));            
		$activeSheet->setCellValue('E'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=131,$conditions['dateFrom'],$conditions['dateTo'],$baseon));   
        $activeSheet->setCellValue('F'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=211,$conditions['dateFrom'],$conditions['dateTo'],$baseon));            
		$activeSheet->setCellValue('G'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=191,$conditions['dateFrom'],$conditions['dateTo'],$baseon));            
		$activeSheet->setCellValue('H'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=121,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
		$activeSheet->setCellValue('I'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=22,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
		$activeSheet->setCellValue('J'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=234,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
		$activeSheet->setCellValue('K'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=141,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
		$activeSheet->setCellValue('L'.$index, reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=201,$conditions['dateFrom'],$conditions['dateTo'],$baseon));
		$total =  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=111,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=181,$conditions['dateFrom'],$conditions['dateTo'],$baseon)	
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=221,$conditions['dateFrom'],$conditions['dateTo'],$baseon)	
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=131,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=211,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=191,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=201,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=234,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=121,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=141,$conditions['dateFrom'],$conditions['dateTo'],$baseon)
			   +  reportProvider::getTotal($db,$data[$i]->vip_number,$outlet=22,$conditions['dateFrom'],$conditions['dateTo'],$baseon);
			   	
		
		$activeSheet->setCellValue('M'.$index, $total);
		
		$index++;
    }
    unset($data);
}
//echo $size;exit;
$activeSheet->setCellValue('A6' , 'SUM');

$activeSheet->getStyle('B6:M6' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('B6:M6')->getNumberFormat()->setFormatCode("#,##0");
$activeSheet->setCellValue('B6' , "=SUM(B7:B".($size+6).")");
$activeSheet->setCellValue('C6' , "=SUM(C7:C".($size+6).")");
$activeSheet->setCellValue('D6' , "=SUM(D7:D".($size+6).")");
$activeSheet->setCellValue('E6' , "=SUM(E7:E".($size+6).")");
$activeSheet->setCellValue('F6' , "=SUM(F7:F".($size+6).")");
$activeSheet->setCellValue('G6' , "=SUM(G7:G".($size+6).")");
$activeSheet->setCellValue('H6' , "=SUM(H7:H".($size+6).")");
$activeSheet->setCellValue('I6' , "=SUM(I7:I".($size+6).")");
$activeSheet->setCellValue('J6' , "=SUM(J7:J".($size+6).")");
$activeSheet->setCellValue('K6' , "=SUM(K7:K".($size+6).")");
$activeSheet->setCellValue('L6' , "=SUM(L7:L".($size+6).")");
$activeSheet->setCellValue('M6' , "=SUM(M7:M".($size+6).")");
$fill = $activeSheet->getStyle("A6:M6")->getFill();
$fill->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$fill->getStartColor()->setARGB('f7f7f7');
$style = $activeSheet->getStyle('B6:M6')->getFont();
$style->setSize(12);
$style->setBold(true);


//exit;
$fileName = 'actual_paid_list_report_'.date('m_d_Y_H_i_s');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(JPATH_BASE.DS.'tmp'.DS.$fileName.'.xlsx');

$fullPath = JPATH_ROOT.DS.'tmp'.DS.$fileName.'.xlsx';
$fsize = filesize($fullPath);

header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename="'.$fileName.'.xlsx"');
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
header('Content-Length: ' . $fsize);
ob_clean();

flush();
readfile( $fullPath );
exit;          
function setBackgroundColor(&$activeSheet, $colName, $colIndex, $width=15)
{      
    $activeSheet->getColumnDimension($colName)->setWidth($width);       
    $style = $activeSheet->getStyle($colName.$colIndex);
    $style->getFont()->setSize(12);
    //$style->getFont()->setBold(true);
    $style->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
